/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ps.packdescription.excel;

import com.ps.packdescription.db.DBManager;
import com.ps.packdescription.translation.Translation;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

/**
 *
 * @author srkrishnan
 */
public class ExcelReader {

    static Workbook wb = null;
    static Connection conn = null;

    public static void init() {

        try {
            wb = new HSSFWorkbook(new FileInputStream("sheet.xls"));
        } catch (IOException ex) {
            Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void read() {
        try {
            init();

            conn = DBManager.getConnection();

            // our SQL SELECT query. 
            // if you only need a few columns, specify them by name instead of using "*"
            String query = "select * from ps_lang";

            // create the java statement
            Statement st = conn.createStatement();

            // execute the query, and get a java resultset
            ResultSet rs = st.executeQuery(query);

            HashMap<String, Integer> langcodes = new HashMap<String, Integer>();
            String frenchSheet = "";
            // iterate through the java resultset
            while (rs.next()) {
                langcodes.put(rs.getString("iso_code"), rs.getInt("id_lang"));
                if ("fr".equals(rs.getString("iso_code"))) {
                    frenchSheet = rs.getString("name");
                }
            }


            Sheet sheet = wb.getSheet(frenchSheet);

            int langid = 0;

            for (Row row : sheet) {

                int productid = 0;
                String packdescription = "";

                Cell cell = row.getCell(1);
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");

                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.println(cell.getDateCellValue());
                        } else {
                            System.out.println((long) cell.getNumericCellValue());
                            productid = (int) (long) cell.getNumericCellValue();
                        }
                        break;
                    default:
                        System.out.println();
                }


                cell = row.getCell(12);
                if (cell != null) {
                    cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    System.out.print(cellRef.formatAsString());
                    System.out.print(" - ");

                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.println(cell.getStringCellValue());
                            packdescription = cell.getStringCellValue();
                            break;
                        default:
                            System.out.println();
                    }
                }

                String tmppackdesc = packdescription;
                for (String langcode : langcodes.keySet()) {
                    langid = langcodes.get(langcode);
                    packdescription = parse(tmppackdesc, "fr", langcode);

                    query = "update ps_product_lang "
                            + "set ps_product_lang.pack_description=? "
                            + "where ps_product_lang.id_product=? and ps_product_lang.id_lang=?";

                    PreparedStatement psmt = conn.prepareStatement(query);
                    psmt.setString(1, packdescription);
                    psmt.setInt(2, productid);
                    psmt.setInt(3, langid);


                    ////System.out.println(psmt);

                    psmt.executeUpdate();
                }
                System.out.println();
            }

        } catch (SQLException ex) {
            Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    private static String parse(String packdescription, String fromlang, String tolang) {

        StringTokenizer tokenizer = new StringTokenizer(packdescription, "-");
        String table = "";
        table = "<table style=\"table-layout: fixed; text-align: center;\" border=\"1\" cellspacing=\"0\" cellpadding=\"1\" width=\"100%\">"
                + "<tbody>";
        boolean header = true;
        while (tokenizer.hasMoreTokens()) {
            String token = tokenizer.nextToken();
            System.out.println("============>" + token);
            StringTokenizer datatokenizer = new StringTokenizer(token, ":");

            table += "<tr>";
            if (header) {
                table += "<th></th>";
            }

            while (datatokenizer.hasMoreElements()) {
                String data = datatokenizer.nextToken();
                System.out.println("$$$$$$ " + data);

                if (header) {
                    table += "<th>" + data + "</th>";
                } else {
                    data = translate(data, fromlang, tolang);
                    table += "<td>" + data + "</td>";
                }
            }
            if (header) {
                header = false;
            }
            table += "</tr>";
        }
        table += "</tbody>"
                + "</table>";

        return table;
    }

    private static String translate(String data, String fromlang, String tolang) {
        if (isIntNumber(data)) {
            return data;
        }
        if (Translation.translationmap.containsKey(fromlang+"-"+tolang) == false) {
            HashMap<String,String> transmap = new HashMap<String, String>();
            Translation.translationmap.put(fromlang+"-"+tolang, transmap);
            
        }
        if(Translation.translationmap.get(fromlang+"-"+tolang).containsKey(data)==false)
        {
            Translation.translationmap.get(fromlang+"-"+tolang).put(data, Translation.translate(data, fromlang, tolang));
        }
        return Translation.translationmap.get(fromlang+"-"+tolang).get(data);
        
    }

    public static boolean isIntNumber(String num) {
        try {
            Integer.parseInt(num);
        } catch (NumberFormatException nfe) {
            return false;
        }
        return true;
    }
}
